﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace OALPTool
{
    public class ExportDataGridViewToExcel
    {
        private string sFilePath;

        /// <summary>
        /// The file path for exporting.
        /// </summary>
        public string FilePath
        {
            get { return this.sFilePath; }
            set { this.sFilePath = value; }
        }
        /// <summary>
        /// Export starting event.
        /// </summary>
        public event EventHandler ExportStartingEvent;

        /// <summary>
        /// Export progressing event.
        /// </summary>
        public event EventHandler ExportProgressingEvent;

        /// <summary>
        /// Export ended event.
        /// </summary>
        public event EventHandler<ExportEndedEventArgs> ExportEndedEvent;
        public DataGridView DataGridViewControl = null;

        #region Export DateGridView to csv file
        /// <summary>
        /// Common method, export DataGridView object to a csv file with data.
        /// </summary>
        /// <param name="_stream"></param>
        public void DataGridViewToCSV(object _stream)
        {
            int rowscount = DataGridViewControl.RowCount;
            int colscount = DataGridViewControl.ColumnCount;
            // rows count and columns count should be bigger than zero.
            if (rowscount <= 0 || colscount <= 0)
            {
                ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception("No data for exporting.")));
                return;
            }

            ExportStartingEvent.Invoke(colscount + rowscount, new EventArgs());
            Stream myStream = null;
            StreamWriter sw = null;
            string columnTitle = "";

            try
            {
                myStream = (Stream)_stream;
                sw = new StreamWriter(myStream, System.Text.Encoding.UTF8);

                // output columns title
                for (int i = 0; i < colscount; i++)
                {
                    if (i > 0)
                    {
                        columnTitle += ",";
                    }
                    columnTitle += DataGridViewControl.Columns[i].HeaderText;
                    ExportProgressingEvent.Invoke(i + 1, new EventArgs());
                }
                sw.WriteLine(columnTitle);

                // output data
                for (int j = 0; j < rowscount; j++)
                {
                    string columnValue = "";
                    for (int k = 0; k < colscount; k++)
                    {
                        if (k > 0)
                        {
                            columnValue += ",";
                        }
                        if (DataGridViewControl.Rows[j].Cells[k].Value == null)
                        {
                            columnValue += "";
                        }
                        else
                        {
                            columnValue += DataGridViewControl.Rows[j].Cells[k].Value.ToString().Trim();
                        }
                    }
                    sw.WriteLine(columnValue);
                    ExportProgressingEvent.Invoke(colscount + j + 1, new EventArgs());
                }
            }
            catch (Exception e)
            {
                ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception(string.Format("Exporte file failed.\r\n", e.Message))));
                return;
            }
            finally
            {
                if (sw != null)
                {
                    sw.Close();
                }
                if (myStream != null)
                {
                    myStream.Close();
                }
            }

            ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(true, null));
        }
        #endregion

        #region Export DataGridView to Excel file
        /// <summary> 
        /// Export DataGridView object to a Excel file with data. 
        /// </summary> 
        /// <remarks>
        /// add com "Microsoft Excel 14.0 Object Library"
        /// using Excel=Microsoft.Office.Interop.Excel;
        /// using System.Reflection;
        /// </remarks>
        public void DataGridViewToExcel()
        {
            #region  Validation export parameters
            int rowscount = DataGridViewControl.RowCount;
            int colscount = DataGridViewControl.ColumnCount;
            // rows count and columns count should be bigger than zero.
            if (rowscount <= 0 || colscount <= 0)
            {
                ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception("No data for exporting.")));
                return;
            }

            // rows count should be less than or equal to 65536 .
            if (rowscount > 65536)
            {
                ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception("The rows count should be less than or equal to 65536.")));
                return;
            }

            // columns count should be less than or equal to 255. 
            if (colscount > 255)
            {
                ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception("The columns count should be less than or equal to 255.")));
                return;
            }

            // if current exporting file does exist, delete it.
            if (File.Exists(this.sFilePath))
            {
                try
                {
                    File.Delete(sFilePath);
                }
                catch (Exception error)
                {
                    ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception(string.Format("Cannot delete the file\r\n{0}", error.Message))));
                    return;
                }
            }
            #endregion

            ExportStartingEvent.Invoke(colscount + rowscount, new EventArgs());
            Excel.Application objExcel = null;
            Excel.Workbook objWorkbook = null;
            Excel.Worksheet objsheet = null;
            try
            {
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
                objExcel.Visible = false;

                // output columns tilte
                int displayColumnsCount = 1;
                for (int i = 0; i <= colscount - 1; i++)
                {
                    if (DataGridViewControl.Columns[i].Visible == true)
                    {
                        objExcel.Cells[1, displayColumnsCount] = DataGridViewControl.Columns[i].HeaderText.Trim();
                        displayColumnsCount++;
                    }

                    ExportProgressingEvent.Invoke(i + 1, new EventArgs());
                }

                //output data 
                for (int row = 0; row <= rowscount - 1; row++)
                {
                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (DataGridViewControl.Columns[col].Visible == true)
                        {
                            try
                            {
                                objExcel.Cells[row + 2, displayColumnsCount] = DataGridViewControl.Rows[row].Cells[col].Value.ToString().Trim();
                                displayColumnsCount++;
                            }
                            catch { }
                        }
                    }

                    ExportProgressingEvent.Invoke(colscount + row + 1, new EventArgs());
                }
                //save file
                objWorkbook.SaveAs(this.sFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(false, new Exception(string.Format("Export file failed.\r\n{0}", error.Message))));
                return;
            }
            finally
            {
                //Dispose the Excel related objects 
                if (objWorkbook != null)
                {
                    objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                }
                if (objExcel.Workbooks != null)
                {
                    objExcel.Workbooks.Close();
                }
                if (objExcel != null)
                {
                    objExcel.Quit();
                }

                objsheet = null;
                objWorkbook = null;
                objExcel = null;
                GC.Collect(); // force final cleanup.
            }

            ExportEndedEvent.Invoke(new object(), new ExportEndedEventArgs(true, null));
        }
        #endregion

        /// <summary>
        /// Export ended EventArgs class.
        /// </summary>
        public class ExportEndedEventArgs : EventArgs
        {
            private bool bIsCompleted = false;
            private Exception eErrors = null;

            /// <summary>
            /// If export is completed.
            /// </summary>
            public bool IsCompleted
            {
                get { return this.bIsCompleted; }
            }

            /// <summary>
            /// Errors.
            /// </summary>
            public Exception Errors
            {
                get { return this.eErrors; }
            }

            public ExportEndedEventArgs(bool _isLoaded, Exception _errors)
            {
                this.bIsCompleted = _isLoaded;
                if (_errors != null)
                {
                    this.eErrors = _errors;
                }
            }
        }
    }
}
